package com.pks.autoInsurance.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.pks.autoInsurance.dto.CustomerDto;

/**
 * Implementation class for RegistrationDAO
 * 
 * @author Pankaj Soni
 */

public class CustomerDAOImpl extends AutoInsuranceDAO implements CustomerDAO {

	private static final Logger LOGGER = Logger
			.getLogger(CustomerDAOImpl.class);

	/**
	 * 
	 * @see com.CustomerDAO.autoInsurance.dao.RegistrationDAO#createCustomer(com.cts.autoInsurance
	 *      .dto.PersonalDetailsDto)
	 */
	@Override
	public final boolean createCustomer(final CustomerDto customerDtoReq) {
		LOGGER.debug("begin");
		Connection connection = this.getConnection();
		String sqlReg = "INSERT INTO REGISTRATION"
				+ " VALUES(?,?,?,?,?,?,?,?,?,?,?)";
		String sqlVehicle = "INSERT INTO VEHICLE(SSN,TYPE,MODEL,MAKE,"
				+ "REGNO,POLICY_TYPE,POLICY_AMOUNT,MF_YEAR,TOTAL_ACCIDENT)"
				+ " VALUES(?,'0','NA','NA','NA','NA',0,'1900',0)";
		LOGGER.info("sqlReg: " + sqlReg);
		LOGGER.info("sqlVehicle: " + sqlVehicle);
		try {
			connection.setAutoCommit(false);
			// insert value in registration table
			PreparedStatement preparedStatement = connection
					.prepareStatement(sqlReg);
			java.sql.Date dateofBirth = new java.sql.Date(customerDtoReq
					.getDateOfBirth().getTime());

			preparedStatement.setString(1, customerDtoReq.getSsn());
			preparedStatement.setString(2, customerDtoReq.getPasswd());
			preparedStatement.setString(3, customerDtoReq.getFirstName());
			preparedStatement.setString(4, customerDtoReq.getLastName());
			preparedStatement.setString(5, customerDtoReq.getGender());
			preparedStatement.setDate(6, dateofBirth);
			preparedStatement.setString(7, customerDtoReq.getMobileNo());
			preparedStatement.setString(8, customerDtoReq.getEmail());
			preparedStatement.setString(9, customerDtoReq.getCity());
			preparedStatement.setString(10, customerDtoReq.getBloodGroup());
			preparedStatement.setString(11, customerDtoReq.getDrivingLicence());

			int regRows = preparedStatement.executeUpdate();

			// insert into vehicle
			preparedStatement = connection.prepareStatement(sqlVehicle);
			preparedStatement.setString(1, customerDtoReq.getSsn());
			int vehicleRows = preparedStatement.executeUpdate();
			LOGGER.info("customerRowInserted: " + regRows);
			LOGGER.info(":::vehicleRowInserted:::" + vehicleRows);

		} catch (SQLException e) {
			LOGGER.error(e.getMessage());
			try {
				connection.rollback();
			} catch (SQLException ex) {
				LOGGER.error(e.getMessage());
			}
		} finally {
			try {
				connection.commit();
				connection.close();
			} catch (SQLException e) {
				LOGGER.error(e.getMessage());
			}
		}
		LOGGER.debug("end");
		return true;
	}

	/**
	 * 
	 * @see com.CustomerDAO.autoInsurance.dao.RegistrationDAO#isExistingUser(com.cts.
	 *      autoInsurance.dto.LoginDto)
	 */
	@Override
	public final boolean isExistingUser(final CustomerDto registrationDtoReq) {
		LOGGER.debug("begin");
		Connection connection = this.getConnection();
		String sql = "SELECT * FROM REGISTRATION WHERE SSN=?";
		LOGGER.info("sql: " + sql);
		boolean userExists = false;
		try {
			connection.setAutoCommit(false);
			PreparedStatement preparedStatement = connection
					.prepareStatement(sql);
			preparedStatement.setString(1, registrationDtoReq.getSsn());
			ResultSet resultSet = preparedStatement.executeQuery();
			if (resultSet.next()) {
				userExists = true;
			}
		} catch (SQLException e) {
			LOGGER.error(e.getMessage());
			userExists = false;
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				LOGGER.error(e.getMessage());
			}
		}
		LOGGER.info(":isExistingUser: " + userExists);
		LOGGER.debug("end");
		return userExists;
	}

	/**
	 * @see com.pks.autoInsurance.dao.CustomerDAO#updateCustomer(com.pks.autoInsurance.dto.CustomerDto)
	 */
	@Override
	public final boolean updateCustomer(final CustomerDto customerDtoReq) {
		LOGGER.debug("begin");
		Connection connection = this.getConnection();
		String sql = "UPDATE REGISTRATION "
				+ "SET PASSWD=?,FNAME=?,LNAME=?,GENDER=?,MOBILENO=?,"
				+ "EMAIL=?,CITY=?,BLOOD_GROUP=?,DRIVING_LICENSE=?,"
				+ "DATE_OF_BIRTH=? WHERE SSN=?";
		LOGGER.info("sql: " + sql);
		try {
			connection.setAutoCommit(false);
			// update registration table
			PreparedStatement preparedStatement = connection
					.prepareStatement(sql);
			java.sql.Date dateofBirth = new java.sql.Date(customerDtoReq
					.getDateOfBirth().getTime());
			preparedStatement.setString(1, customerDtoReq.getPasswd());
			preparedStatement.setString(2, customerDtoReq.getFirstName());
			preparedStatement.setString(3, customerDtoReq.getLastName());
			preparedStatement.setString(4, customerDtoReq.getGender());
			preparedStatement.setString(5, customerDtoReq.getMobileNo());
			preparedStatement.setString(6, customerDtoReq.getEmail());
			preparedStatement.setString(7, customerDtoReq.getCity());
			preparedStatement.setString(8, customerDtoReq.getBloodGroup());
			preparedStatement.setString(9, customerDtoReq.getDrivingLicence());
			preparedStatement.setDate(10, dateofBirth);
			preparedStatement.setString(11, customerDtoReq.getSsn());
			int rows = preparedStatement.executeUpdate();
			LOGGER.info(":userProfileUpdated: " + rows);
		} catch (SQLException e) {
			LOGGER.error(e.getMessage());
			try {
				connection.rollback();
			} catch (SQLException ex) {
				LOGGER.error(e.getMessage());
			}
		} finally {
			try {
				connection.commit();
				connection.close();
			} catch (SQLException e) {
				LOGGER.error(e.getMessage());
			}
		}
		LOGGER.debug("end");
		return true;
	}

	/**
	 * @see com.pks.autoInsurance.dao.CustomerDAO#findCustomer(java.lang.String)
	 */
	@Override
	public final CustomerDto findCustomer(final String ssn) {
		LOGGER.debug("begin");
		Connection connection = this.getConnection();
		try {
			connection.setAutoCommit(false);
			String sql = "SELECT SSN,PASSWD,FNAME,LNAME,GENDER,DATE_OF_BIRTH,"
					+ "MOBILENO,EMAIL,CITY,BLOOD_GROUP,DRIVING_LICENSE"
					+ " FROM REGISTRATION WHERE SSN=?";
			LOGGER.info("sql: " + sql);
			PreparedStatement preparedStatement = connection
					.prepareStatement(sql);
			preparedStatement.setString(1, ssn);
			ResultSet resultSet = preparedStatement.executeQuery();
			if (resultSet.next()) {
				CustomerDto regDto = new CustomerDto();
				regDto.setSsn(resultSet.getString("SSN"));
				regDto.setPasswd(resultSet.getString("PASSWD"));
				regDto.setFirstName(resultSet.getString("FNAME"));
				regDto.setLastName(resultSet.getString("LNAME"));
				regDto.setGender(resultSet.getString("GENDER"));
				regDto.setDateOfBirth(resultSet.getDate("DATE_OF_BIRTH"));
				regDto.setMobileNo(resultSet.getString("MOBILENO"));
				regDto.setEmail(resultSet.getString("EMAIL"));
				regDto.setCity(resultSet.getString("CITY"));
				regDto.setBloodGroup(resultSet.getString("BLOOD_GROUP"));
				regDto.setDrivingLicence(resultSet.getString("DRIVING_LICENSE"));
				return regDto;
			}
		} catch (SQLException e) {
			LOGGER.error(e.getMessage());
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				LOGGER.error(e.getMessage());
			}
			LOGGER.debug("end");
		}
		return null;
	}

	/**
	 * @see com.pks.autoInsurance.dao.CustomerDAO#findCustomers()
	 */
	@Override
	public final List<CustomerDto> findCustomers() {
		LOGGER.debug("begin");
		Connection connection = this.getConnection();
		List<CustomerDto> personList = new ArrayList<CustomerDto>();
		try {
			connection.setAutoCommit(false);
			String sql = "SELECT SSN,FNAME,LNAME,MOBILENO,CITY,EMAIL,"
					+ "DATE_OF_BIRTH,GENDER FROM REGISTRATION";
			LOGGER.info("sql: " + sql);
			PreparedStatement preparedStatement = connection
					.prepareStatement(sql);
			ResultSet resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				CustomerDto registrationDto = new CustomerDto();
				registrationDto.setSsn(resultSet.getString("SSN"));
				registrationDto.setFirstName(resultSet.getString("FNAME"));
				registrationDto.setLastName(resultSet.getString("LNAME"));
				registrationDto.setMobileNo(resultSet.getString("MOBILENO"));
				registrationDto.setCity(resultSet.getString("CITY"));
				registrationDto.setEmail(resultSet.getString("EMAIL"));
				registrationDto.setDateOfBirth(resultSet
						.getDate("DATE_OF_BIRTH"));
				registrationDto.setGender(resultSet.getString("GENDER"));
				personList.add(registrationDto);
			}
		} catch (SQLException e) {
			LOGGER.error(e.getMessage());
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				LOGGER.error(e.getMessage());
			}
		}
		LOGGER.debug("end");
		return personList;
	}

	/**
	 * @see com.pks.autoInsurance.dao.CustomerDAO#findCustomers(java.lang.String)
	 */
	@Override
	public final List<CustomerDto> findCustomers(final String ssn) {
		LOGGER.debug("begin");
		LOGGER.info("ssn: " + ssn);
		Connection connection = this.getConnection();
		List<CustomerDto> registrationList = new ArrayList<CustomerDto>();
		CustomerDto customerDto;
		String sql = "SELECT SSN,FNAME,LNAME,MOBILENO,CITY,EMAIL,DATE_OF_BIRTH,GENDER FROM REGISTRATION WHERE SSN LIKE ?";
		LOGGER.info("sql: " + sql);
		try {
			connection.setAutoCommit(false);
			PreparedStatement preparedStatement = connection
					.prepareStatement(sql);
			preparedStatement.setString(1, "%" + ssn + "%");
			ResultSet resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				customerDto = new CustomerDto();
				customerDto.setSsn(resultSet.getString("SSN"));
				customerDto.setFirstName(resultSet.getString("FNAME"));
				customerDto.setLastName(resultSet.getString("LNAME"));
				customerDto.setMobileNo(resultSet.getString("MOBILENO"));
				customerDto.setCity(resultSet.getString("CITY"));
				customerDto.setEmail(resultSet.getString("EMAIL"));
				customerDto.setDateOfBirth(resultSet.getDate("DATE_OF_BIRTH"));
				customerDto.setGender(resultSet.getString("GENDER"));
				registrationList.add(customerDto);
			}
			LOGGER.info("List retrived: " + registrationList);
		} catch (SQLException e) {
			LOGGER.error(e.getMessage());
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				LOGGER.error(e.getMessage());
			}
		}
		LOGGER.debug("end");
		return registrationList;
	}

	/**
	 * @see com.pks.autoInsurance.dao.CustomerDAO#deleteCustomer(java.lang.String)
	 */
	@Override
	public final int deleteCustomer(final String ssn) {
		LOGGER.debug("begin");
		Connection connection = this.getConnection();
		String sql = "DELETE FROM REGISTRATION WHERE SSN=?";
		LOGGER.info("sql: " + sql);
		try {
			connection.setAutoCommit(false);
			PreparedStatement preparedStatement = connection
					.prepareStatement(sql);
			preparedStatement.setString(1, ssn);
			return preparedStatement.executeUpdate();
		} catch (SQLException e) {
			LOGGER.error(e.getMessage());
			try {
				connection.rollback();
			} catch (SQLException ex) {
				LOGGER.error(ex.getMessage());
			}
		} finally {
			try {
				connection.commit();
				connection.close();
			} catch (SQLException e) {
				LOGGER.error(e.getMessage());
			}
		}
		LOGGER.debug("end");
		return 0;
	}

}
